package com.gdit.zax.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.gdit.zax.dao.VideoDao;
import com.gdit.zax.entitys.AddVideo;
import com.gdit.zax.entitys.BaseEntity;
import com.gdit.zax.entitys.Comment;
import com.gdit.zax.entitys.Course;
import com.gdit.zax.entitys.CourseSee;
import com.gdit.zax.entitys.Section;
import com.gdit.zax.entitys.Tag;
import com.gdit.zax.entitys.VideoALL;
import com.gdit.zax.entitys.VideoInfo;
import com.gdit.zax.entitys.VideoRecord;
import com.gdit.zax.factory.DAOFactory;
import com.gdit.zax.utils.DBUtil;
import com.gdit.zax.utils.SqlUtil;

public class VideoDaoImpl implements VideoDao {
	private PreparedStatement ps;
	private ResultSet rs;

	private Connection conn;
	private HttpServletRequest request;
	private HttpServletResponse response;

	public VideoDaoImpl(Connection conn, HttpServletRequest request,
			HttpServletResponse response) {
		
		this.conn = conn;
		this.request = request;
		this.response = response;
	}

	public boolean delVideo(int vid) {
		String sql = "delete from t_video where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, vid);
			int result = ps.executeUpdate();
			if (result > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public VideoInfo getvideoinfo(int vid) {
		String sql = "select * from t_video where Id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, vid);
			
			rs = ps.executeQuery();
			
			if (rs.next()) {
				VideoInfo be = DBUtil.getobject(rs, VideoInfo.class);
				return be;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<VideoALL> listAllvideo(int teacherid, int page,
			int onepagecount) {
		ArrayList<VideoALL> list = new ArrayList<VideoALL>();
		String sql = SqlUtil.getSql("listmAllvideo.sql");
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, teacherid);
			ps.setInt(2, (page - 1) * onepagecount);
			ps.setInt(3, onepagecount);
			
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				VideoALL be = DBUtil.getJoinObject(rs, VideoALL.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<VideoALL> listAllvideo(int page, int onepagecount) {
		ArrayList<VideoALL> list = new ArrayList<VideoALL>();
		String sql = SqlUtil.getSql("listAllvideo.sql");
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1) * onepagecount);
			ps.setInt(2, onepagecount);
			
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				VideoALL be = DBUtil.getJoinObject(rs, VideoALL.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<VideoALL> listvideobyhot(int page, int onepagecount) {
		//按照播放记录数排序
		ArrayList<VideoALL> list = new ArrayList<VideoALL>();
		String basesql = "select *,TO_DAYS(now())-TO_DAYS(v.AddTime)<7 from t_video as v inner join t_section as s on v.SectionId=s.id inner join t_course as c on s.CourseId=c.Id inner join t_videotype as vt on v.type=vt.id inner join t_sectiontype as st on s.type=st.Id inner join t_coursetype as ct on c.type=ct.Id inner join t_user as u on s.AnthorId=u.id ";
		String sql = basesql + "inner join (select VideoId,count(VideoId)  as scount from t_seerecord  group by VideoId order by scount desc limit ?,?) as sc on v.id=sc.VideoId";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1) * onepagecount);
			ps.setInt(2, onepagecount);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				VideoALL be = DBUtil.getJoinObject(rs, VideoALL.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<VideoALL> listvideobynew(int page, int onepagecount) {
		//按照发布时间排序
		ArrayList<VideoALL> list = new ArrayList<VideoALL>();
		String basesql = "select * from t_video as v inner join t_section as s on v.SectionId=s.id inner join t_course as c on s.CourseId=c.Id inner join t_videotype as vt on v.type=vt.id inner join t_sectiontype as st on s.type=st.Id inner join t_coursetype as ct on c.type=ct.Id inner join t_user as u on s.AnthorId=u.id ";
		String sql = basesql + "order by v.AddTime desc limit ?,?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1) * onepagecount);
			ps.setInt(2, onepagecount);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				VideoALL be = DBUtil.getJoinObject(rs, VideoALL.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public boolean addVideo(AddVideo info) {
		String sql;
		try {
			// 查看有无该章节类型，有读，无增
			DAOFactory dao = DAOFactory.init(conn, request, response);
			info.setSectiontype(dao.getSectionDao().addSectionTypeWhetherExist(info.getSectiontype().getName()));
			info.setVideotype(this.addVideoTypeWhetherExist(info.getVideotype().getName()));

			// 开启事务
			conn.setAutoCommit(false);

			//插入课程信息
			sql = "insert into t_course set Name=?,Introduction=?,Anthorid=?,Type=?,Pic=?";
			Course course = info.getCourse();
			BaseEntity ctype = info.getCoursetype();
			ps = conn.prepareStatement(sql);
			ps.setString(1, course.getName());
			ps.setString(2, course.getIntroduction());
			ps.setInt(3, course.getAnthorid());
			ps.setInt(4, ctype.getId());
			ps.setString(5, course.getPic());
			ps.executeUpdate();

			// 查询刚插入的课程信息
			sql = "select * from t_course where Name=? and Introduction=? and Anthorid=? and Type=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, course.getName());
			ps.setString(2, course.getIntroduction());
			ps.setInt(3, course.getAnthorid());
			ps.setInt(4, ctype.getId());
			rs = ps.executeQuery();
			if (rs.next()) {
				course = DBUtil.getobject(rs, Course.class);
			} else {
				
			}

			//插入章节信息
			Section section = info.getSection();
			BaseEntity stype = info.getSectiontype();
			ps = conn
					.prepareStatement("insert into t_section set CourseId=?,Name=?,Introduction=?,Anthorid=?,Type=?");
			ps.setInt(1, course.getId());
			ps.setString(2, section.getName());
			ps.setString(3, section.getIntroduction());
			ps.setInt(4, section.getAnthorid());
			ps.setInt(5, stype.getId());
			ps.executeUpdate();

			// 查询刚插入的课程信息
			sql = "select * from t_section where CourseId=? and Name=? and Introduction=? and Anthorid=? and Type=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, course.getId());
			ps.setString(2, section.getName());
			ps.setString(3, section.getIntroduction());
			ps.setInt(4, section.getAnthorid());
			ps.setInt(5, stype.getId());
			rs = ps.executeQuery();
			if (rs.next()) {
				section = DBUtil.getobject(rs, Section.class);
			} else {
				
			}

			//插入是视频信息
			VideoInfo video = info.getVideo();
			BaseEntity vtype = info.getVideotype();
			ps = conn
					.prepareStatement("insert into t_video set CourseId=?,SectionId=?,Name=?,Introduction=?,Anthorid=?,Type=?,Url=?,Pic=?");
			ps.setInt(1, course.getId());
			ps.setInt(2, section.getId());
			ps.setString(3, video.getName());
			ps.setString(4, video.getIntroduction());
			ps.setInt(5, video.getAnthorId());
			ps.setInt(6, vtype.getId());
			ps.setString(7, video.getUrl());
			ps.setString(8, video.getPic());
			ps.executeUpdate();
			
			
			// 查询刚插入的课程信息
			sql = "select * from t_video where CourseId=? and SectionId=? and Name=? and Introduction=? and Anthorid=? and Type=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, course.getId());
			ps.setInt(2, section.getId());
			ps.setString(3, video.getName());
			ps.setString(4, video.getIntroduction());
			ps.setInt(5, video.getAnthorId());
			ps.setInt(6, vtype.getId());
			rs = ps.executeQuery();
			if (rs.next()) {
				video = DBUtil.getobject(rs, VideoInfo.class);
			} else {
				
			}
			
			
			//插入标签信息
			ArrayList<Tag> tags = info.getTags();
			if (tags!=null) {
				for (Tag tag:tags) {
					ps = conn.prepareStatement("insert into t_tag set CourseId=?,SectionId=?,VideoId=?,Name=?");
					ps.setInt(1, course.getId());
					ps.setInt(2, section.getId());
					ps.setInt(3, video.getId());
					ps.setString(4,tag.getName());
					ps.executeUpdate();
				}
			}

			// 提交
			conn.commit();

			return true;
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	
	public ArrayList<Section> getAllSectionByUser(int uid) {
		ArrayList<Section> list = new ArrayList<Section>();
		String sql = "select * from t_section where anthorid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, uid);

			rs = ps.executeQuery();

			while (rs.next()) {
				Section be = DBUtil.getobject(rs, Section.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<BaseEntity> getAllVideotype() {
		ArrayList<BaseEntity> list = new ArrayList<BaseEntity>();
		String sql = "select * from t_videotype";

		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				BaseEntity be = DBUtil.getobject(rs, BaseEntity.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public boolean adddownrecord(VideoRecord vr) {
		String sql = "insert t_downrecord set UserId=?,VideoId=?,Show=1";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, vr.getUserId());
			ps.setInt(2, vr.getVideoId());
			int result = ps.executeUpdate();// 插入登录信息
			if (result > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean addseerecord(VideoRecord vr) {
		String sql = "insert t_seerecord set UserId=?,VideoId=?,Show=1";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, vr.getUserId());
			ps.setInt(2, vr.getVideoId());
			int result = ps.executeUpdate();// 插入登录信息
			if (result != 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public VideoInfo getvideoinfobyname(String vname) {
		String sql = "select * from t_video where Url like '%/" + vname + "'";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			if (rs.next()) {
				VideoInfo info = DBUtil.getobject(rs, VideoInfo.class);
				return info;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	public BaseEntity addVideoTypeWhetherExist(String name) {
		String sql = "select * from t_videotype where Name=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();

			if (rs.next()) {
				BaseEntity be = DBUtil.getobject(rs, BaseEntity.class);
				return be;
			}

			sql = "insert t_videotype set Name=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			int resu = ps.executeUpdate();
			if (resu != 0) {
				return addVideoTypeWhetherExist(name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	
	public ArrayList<CourseSee> listCoursebyhot(int page, int onepagecount) {
		ArrayList<CourseSee> list = new ArrayList<CourseSee>();
		String basesql = "select *  from t_course as c inner join t_coursetype as ct on c.type=ct.Id inner join t_user as u on c.AnthorId=u.Id ";
		String sql = basesql + "inner join (select CourseId,count(CourseId)  as scount from t_seerecord  where DATEDIFF(now(),AddTime) < 30  group by CourseId order by scount desc limit ?,? ) as res on c.Id = res.CourseId";
		//30天内的热门
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1) * onepagecount);
			ps.setInt(2, onepagecount);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				CourseSee be = DBUtil.getJoinObject(rs, CourseSee.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	private static int wasGetOnPage1count;
	public ArrayList<CourseSee> listCoursebynew(int page, int onepagecount) {
		ArrayList<CourseSee> list = new ArrayList<CourseSee>();

		String basesql = "select *  from t_course as c inner join t_coursetype as ct on c.type=ct.Id inner join t_user as u on c.AnthorId=u.Id ";
		String sql1 = basesql + "order by c.AddTime desc limit ?,?";
		String sql2 = basesql + "inner join (select CourseId,count(CourseId)  as scount from t_seerecord group by CourseId order by scount limit ?,?) as res on c.Id = res.CourseId order by c.AddTime desc ";
		//最新更新的
		try {
			//第一次调用getJoinObject代码，会保存查询的字段索引，如果这样使用，会获取不到_scount播放次数
			if (page==1) {
				CourseSee belast = new CourseSee();
				ps = conn.prepareStatement(sql2);
				ps.setInt(1, 0);
				ps.setInt(2, 1);
				rs = ps.executeQuery();
				if (rs.next()) {
					belast = DBUtil.getJoinObject(rs, CourseSee.class);
				}
				ps = conn.prepareStatement(sql1);
				ps.setInt(1, 0);
				ps.setInt(2, onepagecount);
				rs = ps.executeQuery();

				while (rs.next()) {
					CourseSee be = DBUtil.getJoinObject(rs, CourseSee.class);
					if (be.getT_course_id() == belast.getT_course_id()) {
						list.add(belast);
					} else {
						list.add(be);
					}
				}
				
				
				//没看的视频不够10个,继续加看了的视频
				if (list.size()<onepagecount) {
					ps = conn.prepareStatement(sql2);
					ps.setInt(1, 1);
					ps.setInt(2, onepagecount - list.size());
					rs = ps.executeQuery();
					
					int ucount = 0;
					while (rs.next()) {
						ucount++;
						CourseSee be = DBUtil.getJoinObject(rs, CourseSee.class);
						list.add(be);
					}
					wasGetOnPage1count = ucount;
				}
			} else if (page > 1){
				ps = conn.prepareStatement(sql2);
				ps.setInt(1, (page - 1) * onepagecount + wasGetOnPage1count);//第二页的时候,忽略在第一页已经获取的数量
				ps.setInt(2, onepagecount);
				rs = ps.executeQuery();
				while (rs.next()) {
					CourseSee be = DBUtil.getJoinObject(rs, CourseSee.class);
					list.add(be);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 select *  from t_course as c
	inner join t_coursetype as ct on c.type=ct.Id
	inner join t_user as u on c.AnthorId=u.Id
	inner join (
		select sc.CourseId,count(t.Name)  as scount ,t.Name as typename from t_seerecord as sc
		inner join t_tag as t on t.VideoId=sc.VideoId
		where sc.Userid=137 --可选用户id
		group by t.Name order by scount desc limit 0,5 --热门的前5个
	) as res on c.Id = res.CourseId
	
	select sc.CourseId,count(t.Name)  as scount ,t.Name as typename  from t_seerecord as sc
	inner join t_tag as t on t.CourseId=sc.CourseId and t.VideoId=sc.VideoId
	group by sc.CourseId order by scount desc limit 0,5 
	
	
	 */
	//按照浏览量/发布小时计算
	public ArrayList<CourseSee> listCoursebyrecomment(int page, int onepagecount) {
		ArrayList<CourseSee> list = new ArrayList<CourseSee>();
		String basesql = "select *  from t_course as c inner join t_coursetype as ct on c.type=ct.Id inner join t_user as u on c.AnthorId=u.Id ";
		String sql = basesql + "inner join (select sc.CourseId,count(t.Name)  as scount ,t.Name as typename from t_seerecord as sc inner join t_tag as t on t.CourseId=sc.CourseId and t.VideoId=sc.VideoId group by t.Name order by scount desc limit 0,5) as res on c.Id = res.CourseId limit ?,?";
		//30天内的热门
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1) * onepagecount);
			ps.setInt(2, onepagecount);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				CourseSee be = DBUtil.getJoinObject(rs, CourseSee.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<Section> getAllSection(int courseid) {
		ArrayList<Section> list = new ArrayList<Section>();
		String sql = "select * from t_section where CourseId=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, courseid);
			while (rs.next()) {
				Section be = DBUtil.getobject(rs, Section.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<VideoInfo> getAllVideo(int sectionid) {
		ArrayList<VideoInfo> list = new ArrayList<VideoInfo>();
		String sql = "select * from t_video where SectionId=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, sectionid);
			while (rs.next()) {
				VideoInfo be = DBUtil.getobject(rs, VideoInfo.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	

	public ArrayList<VideoALL> listvideobyrecomment(int page, int onepagecount) {
		// TODO Auto-generated method stub
		return null;
	}

	public ArrayList<VideoInfo> listvideos(int teacherid, int page,
			int onepagecount) {
		// TODO Auto-generated method stub
		return null;
	}

	public ArrayList<VideoInfo> listvideos(int page, int onepagecount) {
		// TODO Auto-generated method stub
		return null;
	}

	public ArrayList<CourseSee> listCoursebyrecomment(int uid, int page,
			int onepagecount) {
		ArrayList<CourseSee> list = new ArrayList<CourseSee>();
		String basesql = "select *  from t_course as c inner join t_coursetype as ct on c.type=ct.Id inner join t_user as u on c.AnthorId=u.Id ";
		String sql = basesql + "inner join (select sc.CourseId,count(t.Name)  as scount ,t.Name as typename from t_seerecord as sc inner join t_tag as t on t.CourseId=sc.CourseId and t.VideoId=sc.VideoId where sc.Userid=? group by t.Name order by scount desc limit 0,5) as res on c.Id = res.CourseId limit ?,?";
		//30天内的热门
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, uid);
			ps.setInt(2, (page - 1) * onepagecount);
			ps.setInt(3, onepagecount);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				CourseSee be = DBUtil.getJoinObject(rs, CourseSee.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<VideoALL> listAllVideobycourse(int cid) {
		return listAllVideobycourse(cid,1,10000);
	}
	
	public ArrayList<VideoALL> listAllVideobycourse(int cid,int page,int onepagecount) {
		ArrayList<VideoALL> list = new ArrayList<VideoALL>();
//		String basesql = "select * from t_video as v inner join t_section as s on v.SectionId=s.id inner join t_course as c on s.CourseId=c.Id inner join t_videotype as vt on v.type=vt.id inner join t_sectiontype as st on s.type=st.Id inner join t_coursetype as ct on c.type=ct.Id inner join t_user as u on s.AnthorId=u.id ";
//		String sql = basesql + "where v.CourseId=? limit ?,?";
		String sql = SqlUtil.getSql("liatAllViedoByCourse.sql");
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,cid);
			ps.setInt(2,(page-1)*onepagecount); 
			ps.setInt(3,onepagecount);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				VideoALL be = DBUtil.getJoinObject(rs, VideoALL.class);
				list.add(be);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	

	public VideoALL getVideoAllInfo(int vid) {
		String sql = SqlUtil.getSql("getVideoAllInfo.sql");
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,vid);
			
			rs = ps.executeQuery();
			if (rs.next()) {
				return DBUtil.getJoinObject(rs, VideoALL.class);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}

	public ArrayList<Comment> listAllComment(int page, int onepagecount) {
		// TODO Auto-generated method stub
		return null;
	}

	public ArrayList<Comment> listComment(int vid, int page, int onepagecount) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean editVideoInfo(VideoInfo info) {
//		sql = "update t_user set Accesstoken=?,logintime=now() where Id=?";	
//		String sql = "update t_video set Accesstoken=?,logintime=now() where Id=?";
		return DBUtil.saveobject(conn, "t_video", "id=" + info.getId(), info);
	}

	public Section getSectionById(int sid) {
		String sql = "select * from t_section where Id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, sid);
			rs = ps.executeQuery();
			if (rs.next()) {
				return DBUtil.getobject(rs, Section.class);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public Section getSectionByName(String name) {
		String sql = "select * from t_section where Name=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			if (rs.next()) {
				return DBUtil.getobject(rs, Section.class);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}
